# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from mztr.items import *
import pymysql
import re
import json

# 获取餐厅评论信息
class PylpPipeline:
	# 获取数据库连接
	def open_spider(self, spider):
		try :
			db = spider.settings.get('MYSQL_DB_NAME','test')
			host = spider.settings.get('MYSQL_HOST', 'localhost')
			port = spider.settings.get('MYSQL_PORT', 3306)
			user = spider.settings.get('MYSQL_USER', 'root')
			passwd = spider.settings.get('MYSQL_PASSWORD', '1234')

			# 建立数据库连接
			self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset='utf8')
			# 获取游标
			self.cursor = self.db_conn.cursor()
			print("mysql connet successful")
		except:
			print("pymysql connect have something wrong")


	# 关闭数据库连接
	def close_spider(self,spider):
		self.cursor.close()
		self.db_conn.close()

class FoodPipeline:
	# 获取数据库连接
	def open_spider(self, spider):
		try :
			db = spider.settings.get('MYSQL_DB_NAME','test')
			host = spider.settings.get('MYSQL_HOST', 'localhost')
			port = spider.settings.get('MYSQL_PORT', 3306)
			user = spider.settings.get('MYSQL_USER', 'root')
			passwd = spider.settings.get('MYSQL_PASSWORD', '1234')

			# 建立数据库连接
			self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset='utf8')
			# 获取游标
			self.cursor = self.db_conn.cursor()
			pirnt("mysql connet successful")
		except:
			print("pymysql connect have something wrong")





	# 关闭数据库连接
	def close_spider(self,spider):
		self.cursor.close()
		self.db_conn.close()




# 获取餐厅基本信息
class MztrPipeline:
	# 获取数据库连接
	def open_spider(self, spider):
		try :
			db = spider.settings.get('MYSQL_DB_NAME')
			host = spider.settings.get('MYSQL_HOST')
			port = spider.settings.get('MYSQL_PORT')
			user = spider.settings.get('MYSQL_USER')
			passwd = spider.settings.get('MYSQL_PASSWORD')

			# 建立数据库连接
			self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset='utf8')
			# 获取游标
			self.cursor = self.db_conn.cursor()
			print("mysql connet successful")
		except:
			print("pymysql connect have something wrong")

	# 数据处理
	def process_item(self,item,spider):
		if isinstance(item,StrItem):
			print("insert_data_comments")
			self.insert_data_comments(item)

		if isinstance(item,MztrItem):
			self.insert_data_shop(item)

		return item

	def insert_data_comments(self,item):
		try:
			self.cursor.execute("update mztr set commentTags=%s where shopId="+str(item['sid']),item['commentTags'])
			self.cursor.execute("update mztr set comments=%s where shopId="+str(item['sid']),item['comments'])
			self.db_conn.commit()
			print("insert successful")
		except:
			print("insert failure")
			self.db_conn.rollback()		# 回滚数据


		# if isinstance(item,MztrItem):
		# 	# 将列表转换成json格式

		# 	# 存在手机号时，通过“/”分割字符串
		# 	item['phone'] = josn.dumps(item['phone'].split("/"))
		# 	# 存储商品列表中的所有商品信息
		# 	temp = []
		# 	for i in item['dealList']:
		# 		foodItem = FoodItem()
		# 		foodItem['foodId'] = i['id']
		# 		foodItem['name'] = i['title']
		# 		foodItem['soldNum'] = i['soldNum']
		# 		foodItem['price'] = i['price']
		# 		foodItem['value'] = i['value']
		# 		foodItem['frontImgUrl'] = i['frontImgUrl']
		# 		temp.append(i['id'])
		# 		yield self.insert_data_food(foodItem)
		# 	item['dealList'] = json.dumps(temp)	# 获取当前餐厅食物id列表
		# 	for i in item['extraInfos']:
		# 		temp.append(i['text'])
		# 	item['extraInfos'] = temp
		# 	yield self.insert_data_shop(item)
		# return item



	# #向food表中插入数据
	# def insert_data_food(self,item,spider):
	# 	if isinstance(item,FoodItem):
	# 		data=(	
	# 			item['foodId'],
	# 			item['name'],
	# 			item['soldNum'],
	# 			item['price'],
	# 			item['value'],
	# 			item['frontImgUrl']
	# 			)
	# 		sql = "insert into food values(%s,%s,%s,%s,%s,%s)"
	# 		try:
	# 			# 提交插入数据
	# 			self.cursor.execute(sql,data)
	# 			self.db_conn.commit()
	# 			print("insert OK")
	# 		except :
	# 			print("db commit wrong")	# 报错
	# 			self.db_conn.rollback()		# 回滚数据


	# 插入餐厅数据
	def insert_data_shop(self,item):
		data = (None,
			item['shopId'],
			item['shopName'],
			item['cityName'],
			# item['shopstStyle'],
			item['avgScore'],
			item['allCommentNum'],
			item['address'],
			item['avgPrice'],
			item['frontimg'],
			# item['commentTags'],
			# item['comments']
			# item['dealList'],
			# item['phone'],
			# item['businessHours'],
			# item['extraInfos']
			)
		# ,dealList,phone,businessHours,extraInfos
		sql = "insert into pds(id,shopId,shopName,cityName,avgScore,allCommentNum,address,avgPrice,frontimg) \
				values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
		try:
			# 提交插入数据
			self.cursor.execute(sql,data)
			self.db_conn.commit()
			print("insert OK")
		except :
			print("db commit wrong")	# 报错
			self.db_conn.rollback()		# 回滚数据

	# 关闭数据库连接
	def close_spider(self,spider):
		self.cursor.close()
		self.db_conn.close()
